var conn = require('../javascripts/connect');
var calculation1 = {};
calculation1.cal = async function (depYM) {//3级巴计算
    console.log("depYM", depYM);
    let needCal = [];
    let depFiled = null;
    //去年同期实际金额计算
    needCal[0] = `CALL Calculation('${depYM}','去年同期抓取')`;
    needCal[1] = `CALL Calculation('${depYM}','去年同期调整')`;
    needCal[2] = `UPDATE ${depYM} SET 去年同期调整=0 WHERE 行号>143`;
    needCal[3] = `CALL colSUM('${depYM}','去年同期实际值','去年同期抓取','去年同期调整')`;
    needCal[4] = `CALL Calculation('${depYM}','去年同期实际值')`;
    needCal[5] = `CALL RatioEva('${depYM}','去年同期比率','去年同期实际值')`;
    needCal[6] = `UPDATE ${depYM} SET 去年同期比率=0 WHERE 行号>143`;
    //去年同期累计金额计算
    let [depYM0, year, month] = depYM.split("_");
    let monthpanduan = month;//当为1月份的时候判断使用,累计金额不能取上个月的累计值应该是0
    month = Number(month) > 1 ? Number(month) - 1 : 12;
    year = Number(month) > 1 ? year : year - 1;
    let depYM1 = `${depYM0}_${year}_${month}`;
    // colRecount('淮南项目部_2018_7','淮南项目部_2018_6','去年同期累计','去年同期实际值')
    if (monthpanduan > 1) {
        needCal[7] = `CALL colRecount('${depYM}','${depYM1}','去年同期累计','去年同期实际值')`;
    } else {
        needCal[7] = `update ${depYM} set 去年同期累计 = 去年同期实际值`;
    }
    needCal[8] = `CALL Calculation('${depYM}','去年同期累计')`;
    depFiled = "去年同期累计";
    needCal[9] = `UPDATE ${depYM} SET ${depFiled}=(SELECT IF(d.${depFiled}=0, d.${depFiled},t.${depFiled} / d.${depFiled}/4) FROM (SELECT * FROM ${depYM} WHERE 行号=62) t,(SELECT * FROM ${depYM} WHERE 行号=143) d) WHERE 行号=144`;
    needCal[10] = `CALL RatioEva('${depYM}','去年同期累计比率','去年同期累计')`;
    needCal[11] = `UPDATE ${depYM} SET 去年同期累计比率=0 WHERE 行号>143`;


    //年度计算金额计算
    needCal[12] = `CALL Calculation('${depYM}','年度计划金额')`;
    depFiled = "年度计划金额";
    needCal[13] = `UPDATE ${depYM} SET ${depFiled}=(SELECT IF(d.${depFiled}=0, d.${depFiled},t.${depFiled} / d.${depFiled}/4) FROM (SELECT * FROM ${depYM} WHERE 行号=62) t,(SELECT * FROM ${depYM} WHERE 行号=143) d) WHERE 行号=144`;
    needCal[14] = `CALL RatioEva('${depYM}','年度计划金额比率','年度计划金额')`;
    needCal[15] = `UPDATE ${depYM} SET 年度计划金额比率=0 WHERE 行号>143`;
    //上月实际金额计算
    needCal[16] = `CALL Calculation('${depYM}','上月抓取值')`;
    needCal[17] = `CALL Calculation('${depYM}','上月调整值')`;
    needCal[18] = `UPDATE ${depYM} SET 上月调整值=0 WHERE 行号>143`;
    needCal[19] = `CALL colSUM('${depYM}','上月实际值','上月抓取值','上月调整值')`;
    needCal[20] = `CALL Calculation('${depYM}','上月实际值')`;
    needCal[21] = `CALL RatioEva('${depYM}','上月比率','上月实际值')`;
    needCal[22] = `UPDATE ${depYM} SET 上月比率=0 WHERE 行号>143`;
    //当月计划金额计算
    needCal[23] = `CALL Calculation('${depYM}','当月计划值')`;
    needCal[24] = `CALL RatioEva('${depYM}','当月计划比率','当月计划值')`;
    needCal[25] = `UPDATE ${depYM} SET 当月计划比率=0 WHERE 行号>143`;

    //当月实际金额计算
    needCal[26] = `CALL Calculation('${depYM}','当月抓取值')`;
    needCal[27] = `CALL Calculation('${depYM}','当月调整值')`;
    needCal[28] = `UPDATE ${depYM} SET 当月调整值=0 WHERE 行号>143`;
    needCal[29] = `CALL colSUM('${depYM}','当月实际值','当月抓取值','当月调整值')`;
    needCal[30] = `CALL Calculation('${depYM}','当月实际值')`;
    needCal[31] = `CALL RatioEva('${depYM}','当月比率值','当月实际值')`;
    needCal[32] = `UPDATE ${depYM} SET 当月比率值=0 WHERE 行号>143`;
    needCal[33] = `CALL colDivision('${depYM}','当月实际完成率','当月实际值','当月计划值')`;
    needCal[34] = `UPDATE ${depYM} SET 当月实际完成率=0 WHERE 行号>143`;
    //当月累计金额计算
    if (monthpanduan > 1) {
        needCal[35] = `CALL colRecount('${depYM}','${depYM1}','当月累计金额','当月实际值')`;
    } else {
        needCal[35] = `update ${depYM} set 当月累计金额 =当月实际值`;//如果是1月直接进行计算
    }
    needCal[36] = `CALL Calculation('${depYM}','当月累计金额')`;
    depFiled = "当月累计金额";
    needCal[37] = `UPDATE ${depYM} SET ${depFiled}=(SELECT IF(d.${depFiled}=0, d.${depFiled},t.${depFiled} / d.${depFiled}/4) FROM (SELECT * FROM ${depYM} WHERE 行号=62) t,(SELECT * FROM ${depYM} WHERE 行号=143) d) WHERE 行号=144`;
    needCal[38] = `CALL RatioEva('${depYM}','当月累计比率','当月累计金额')`;
    needCal[39] = `UPDATE ${depYM} SET 当月累计比率=0 WHERE 行号>143`;
    needCal[40] = `CALL colDivision('${depYM}','当月累计完成率','当月累计金额','年度计划金额')`;
    needCal[41] = `UPDATE ${depYM} SET 当月累计完成率=0 WHERE 行号>143`;

    //累比增减额
    needCal[42] = `CALL colReduce('${depYM}','累比增减额','当月累计金额','去年同期累计')`;
    needCal[43] = `CALL Calculation('${depYM}','累比增减额')`;
    needCal[44] = `UPDATE ${depYM} SET 累比增减额=当月累计金额-去年同期累计 WHERE 行号>143`;

    //累比增减率
    needCal[45] = `CALL colDivision('${depYM}','累比增减率','累比增减额','去年同期累计')`;
    needCal[46] = `UPDATE ${depYM} SET 累比增减率=0 WHERE 行号>143`;
    //环比
    needCal[47] = `CALL colReduce('${depYM}','环比金额','当月实际值','上月实际值')`;
    needCal[48] = `CALL colDivision('${depYM}','环比增降幅','环比金额','上月实际值')`;
    needCal[49] = `UPDATE ${depYM} SET 环比增降幅=0 WHERE 行号>143`;
    //同比
    needCal[50] = `CALL colReduce('${depYM}','同比金额','当月实际值','去年同期实际值')`;
    needCal[51] = `CALL colDivision('${depYM}','同比增降幅','同比金额','去年同期实际值')`;
    needCal[52] = `UPDATE ${depYM} SET 同比增降幅=0 WHERE 行号>143`;
    //计划比
    needCal[53] = `CALL colReduce('${depYM}','计划比金额','当月实际值','当月计划值')`;
    needCal[54] = `CALL colDivision('${depYM}','计划增降幅','计划比金额','当月计划值')`;
    needCal[55] = `UPDATE ${depYM} SET 计划增降幅=0 WHERE 行号>143`;
    //下月计划
    needCal[56] = `CALL Calculation('${depYM}','下月计划值')`;
    needCal[57] = `CALL RatioEva('${depYM}','下月比率值','下月计划值')`;
    needCal[58] = `UPDATE ${depYM} SET 下月比率值=0 WHERE 行号>143`;
    for (let i = 0; i < needCal.length; i++) {
        await (async function (i) {
            await new Promise((res1, rej1) => {

                console.log("needCal[i]", i, needCal[i]);
                conn.connection2.query(needCal[i], function (err, result, files) {
                    if (err) {
                        console.log('[ERROR UPDATE]', err.message);
                        return;
                    }
                    console.log("计算成功", result);
                    res1(true);
                })
            })
        })(i)
    }
    return true;
}
module.exports = {
    calculation1
};
